package com.example.mywechatprogram.mapper;

import com.example.mywechatprogram.entity.Rank;
import com.example.mywechatprogram.entity.TotalRank;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface RankMapper {
    @Insert("insert into daily_rank (users_nickname,date,quantity) values (#{users_nickname},#{date},0)")
    int add(@Param("users_nickname") String nickname, @Param("date") String date);
    @Update("update daily_rank set quantity=quantity+1 where users_nickname=#{nickname} and date=#{date}")
    int update(@Param("nickname") String nickname,@Param("date") String date);

    @Select("select quantity from daily_rank where users_nickname=#{nickname} and date=#{date}")
    Integer queryToday(@Param("nickname") String nickname,@Param("date") String date);

    @Select("select * from daily_rank where date=#{date} order by quantity")
    List<Rank> query(@Param("date") String date);

    @Update("update total_rank set total_quantity=total_quantity+1 where users_nickname=#{nickname} and date=#{date}")
    int updateTotalRank(@Param("nickname") String nickname,@Param("date") String date);

    @Insert("insert into total_rank (users_nickname,date,total_quantity) values (#{users_nickname},#{date},1)")
    int insertTotalRank(@Param("users_nickname") String nickname, @Param("date") String date);

    @Select("SELECT users_nickname,SUM( CASE WHEN DATE BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() THEN total_quantity ELSE 0 END ) AS total_quantity FROM total_rank GROUP BY users_nickname ORDER BY total_quantity DESC")
    List<TotalRank> queryTotalRank();
}
